<?php

require_once "tools/spUtil.php";

class spFormDatabase
{
	private 			$formParts;
	private 			$arFormErrors = array();
	static protected 	$table = "";
	private				$formbuilder;
	private				$prefix = "";
	
	protected 			$metaData = array();
	
	public function __construct( $id=false, $singlerow=false, $no_init=false )
	{
		if ( $no_init==true )
			return;
			
		$this->init( $id, $singlerow );	
	}
	
	public function setPrefix( $prefix )
	{
		$this->prefix = $prefix;
	}
	
	public function getPrefix()
	{
		return $this->prefix;
	}
	
	public function setMetaData( $name, $value )
	{
		$this->metaData[$name] = $value;
	}
	
	public function getMetaData( $name, $default=false )
	{
		if ( isset($this->metaData[$name]) )
			return $this->metaData[$name];
			
		return $default;
	}
	
	public function init( $id=false, $singlerow=false )
	{
		if ( !class_exists( "R" ) )
		{
			echo "ERROR: spFormDatabase -> redbeanphp has not been initialized (attempting: ".static::getTable().")</br>";
			return;
		}
	
		$this->formParts = $this->createFormParts();
		
		if ( $singlerow )
		{ 
			$ar = $this->getRowsSql(array(
				"cols" => array(
					"id"
				)
			));
		
			if ( count($ar) )
			{
				$a = $ar[0];
				$id = $a["id"];
			}
			else
			{
				$this->bean = R::dispense( static::getTable() );
				$this->createDefaultValues();
				$id = $this->save();
			}
		}
		
		if ( is_array($id) )
		{
			$list = $this->getRowsWhere($id);
			$id = $list[0]["id"];
		}

		if ( $id==false )
			$this->bean = R::dispense( static::getTable() );
		else
		{
			$this->bean = R::load( static::getTable(), $id );
			$this->updateFormParts();
		}		
	}
	
	public function oneToManyDefinition()
	{
		// override this method to define how a one to many relationships (where one is this object)
		return array();
	}
	
	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	//
	// getIdentityTitle
	//	
	//		this text is used as an identifier for the table.. for example; if a table has a firstname, middlename and lastname columns. Then
	//		this method would return the full name as firstname+middlename+lastname. This is done by the overriding class that defines the table
	//
	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getIdentityTitle()
	{
		return "ERROR: getIdentityTitle not set for database: ".static::getTable()."<br/>";
	}

	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	//
	//	getName
	//
	//		Used to show users what they are creating. 
	//
	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

	static public function getName()
	{
		return "ERROR: spFormDatabase::getName needs to be overrided for database: ".static::getTable()."<br/>";
	}

	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	//
	//	getSqlIdentity
	//
	//		the column that is used as an identity for this object.
	//
	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

	static public function getSqlIdentity()
	{
		echo "ERROR: spFormDatabase::getSqlIdentity needs to be overrided for database: ".static::getTable()."<br/>";
		return "id";
	}
	
	public function setFormBuilder( $formbuilder )
	{
		$this->formbuilder = $formbuilder;
	}

	public function getFormBuilder()
	{
		return $this->formbuilder;
	}
	
	public function createFormBuilder()
	{
		$fbclass = $this->getFormBuilder();
		
		return new $fbclass($this);
	}
	
	static public function getTable()
	{
		return static::$table;
	}
	
	protected function createDefaultValues()
	{
	}

	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	//
	//	createFormParts
	//
	//		defines all the parts that make up a form. The basic structure is:
	//
	//	array(										: array of parts
	//		"partname" => array(					: part name, you can include as many components as you want
	//			array(								: component
	//				"title" 	=> "your title",
	//				"type"		=> "component type i.e. string, date",
	//				"readonly"	=> "true or false",
	//				"save"		=> "true or false if this value is to be saved",
	//				"sql_col"	=> "the sql column name for this value",
	//				"required"	=> "true or false if this value is required",
	//				"valid"		=> "true or false if this valie is valid default: true"
	//			)
	//		)
	//	)
	//
	///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	public function createFormParts()
	{
		return array(
			"id" => array(array(
				"title"	 	=> "Unique ID",
				"type"		=> "string",
				"readonly"	=> "true",
				"save"		=> "false",
				"sql_col"	=> "id"
			)),
			"trashed" => array(array(
				"title"	 	=> "Trashed",
				"type"		=> "string",
				"readonly"	=> "true",
				"sql_col"	=> "trashed"
			))
		);
	}
	
	public function noDisplayPart( $sql_col )
	{
		// update form parts
		foreach ( $this->formParts as &$parts )
		{
			foreach( $parts as &$p )
			{
				if ( isset($p["sql_col"]) && $p["sql_col"]==$sql_col )
					$p["displaypart"] = false;
			}
		}		
		
		return false;
	}
	
	public function setFormParts( $formParts )
	{
		$this->formParts = $formParts;
	}
	
	public function getFormParts( $arFilter=array() )
	{
		$formparts = $this->formParts;
		
		if ( isset($arFilter["remove"]) )
		{
			$filter = $arFilter["remove"];
			foreach( $formparts as $k => $v )
			{
				if ( in_array( $k, $filter ) )
				{
					unset( $formparts[$k] );
				}
			}
		}
		
		return $formparts;
	}
	
	public function findFormPart( $part_name )
	{
		if ( !count($this->formParts) )
			return false;
	
		foreach ( $this->formParts as $key => $p )
		{
			if ( $key==$part_name )
				return $p;
		}
		
		return false;
	}	
	
	public function updateFromRequestCustom( $p )
	{
	}
	
	private function updateFormParts()
	{
		// update form parts
		foreach ( $this->formParts as &$parts )
		{
			foreach( $parts as &$p )
			{
				if ( isset($p["sql_col"]) )
				{
					$sql_col = $p["sql_col"];
					
					if ( $p["type"]=="element" )
					{
						$value = $this->bean->{$sql_col};
						$p["element"]->setElementDataString( $value );
						$p["value"] = $value;
						continue;
					}
				
					$p["value"] = $this->bean->{$sql_col};
				}
			}
		}
	}
	
	public function updateFromRequest()
	{
		$_REQUEST = unMagicQuotify($_REQUEST);
		//spDump( "test", $_REQUEST );
		
		foreach ( $this->formParts as &$parts )
		{
			foreach( $parts as &$p )
			{
				if ( isset($p["type"]) )
				{
					if ( $p["type"]=="checkbox" )
						$p["value"] = "off";
					
					if ( $p["type"]=="custom" )
					{
						$this->updateFromRequestCustom( $p );
						continue;
					}

					if ( $p["type"]=="element" )
					{
						if ( !isset($p["sql_col"]) )
							continue;
							
						$sql_col = $p["sql_col"];
						
						if ( !isset($_REQUEST[$sql_col]) )
							continue;
						
						$value = $_REQUEST[$sql_col];

						// the request value is an array.. we need to handle this					
						if ( is_array($value) )
						{
							$value = spImplode( ",", $value );
						}
					
						$p["element"]->setElementDataString( $value );
						//spDump( "test2", $value );
						
						continue;
					}
				}
			
				if ( !isset($p["sql_col"]) )
					continue;

				$sql_col = $p["sql_col"];
				
				// we never change the id,.. this is handled my sql
				if ( $sql_col=="id" )
					continue;
				
				if ( isset($_REQUEST[$this->prefix.$sql_col]) )
				{
					$p["value"] = $_REQUEST[$this->prefix.$sql_col];

					// the request value is an array.. we need to handle this					
					if ( is_array($p["value"]) )
					{
						$p["value"] = implode( ",", $p["value"] );
					}
				}
			}
		}	
		
		//spDump( "REQUEST", $_REQUEST );
		//spDump( "formParts", $this->formParts );
	}	
	
	protected function validateValue( &$subpart )
	{
		// we assume validation is always good,.. override if you think otherwise :)
		return true;
	}
	
	protected function modifySaveValue( $subpart )
	{
		// if not overrided then we just return the original value
		return $subpart["value"];
	}
	
	public function save()
	{
		//echo "spFormDatabase.php save called";
		//spDump( "REQUEST", $_REQUEST );
		//spDump( "formParts", $this->formParts );
		//return false;
	
		$this->arFormErrors = array();
		
		//var_dump( $this->formParts );
		
		// step through each known sql_col defined
		foreach ( $this->formParts as &$parts )
		{
			foreach( $parts as &$p )
			{
				if ( !isset($p["sql_col"]) )
					continue;

				if ( isset($p["save"]) && $p["save"]==false )
					continue;
					
				$required 	= false;
				if ( isset($p["required"]) && $p["required"]=="true" )
					$required = true;
				
				$sql_col 	= $p["sql_col"];
				$value 		= isset($p["value"])?$p["value"]:"";
				
				if ( $p["type"]=="element" )
				{
					$value = $p["element"]->getElementDataString();
				}
				
				// do some validate checking
				if ( !$this->validateValue( $p ) )
				{
					$p["valid"] = false;
					$this->arFormErrors[] = $sql_col;
					continue;
				}
		
				// check required
				if ( $required )
				{
					if ( $value=="" )
					{
						$p["valid"] = false;
						$this->arFormErrors[] = $sql_col;
					}
				}
				
				$p["value"] = $value;
				$value = $this->modifySaveValue( $p );
				
				$this->bean->{$sql_col} = $value;
				
				//echo "TYPE: ".gettype($value)."<br/>";

				// make sure a string is a string
				if ( $p["type"]=="string" )
					$this->bean->setMeta( "cast.".$sql_col, "string" );

				if ( $p["type"]=="password" )
					$this->bean->setMeta( "cast.".$sql_col, "string" );
					
				if ( $p["type"]=="textarea" )
					$this->bean->setMeta( "cast.".$sql_col, "text" );					
			}		
		}

		if ( count($this->arFormErrors) )
		{
			return false;
		}
		
		// returns the id of the row in the sql database
		return R::store( $this->bean );	
	}	
	
	public function getFormErrors()
	{
		return $this->arFormErrors;
	}	
	
	////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	//
	//	getSqlColumns
	//
	//		returns valid columns that are in sql and are defined in our program
	//
	////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getSqlColumns()
	{
		$table = static::getTable();
		$arSqlCols = R::getRow( "SELECT * FROM ".$table." LIMIT 1" );
		if ( !is_array($arSqlCols) )
			return "*";
	
		$cols = "";
	
		// step through each known sql_col defined
		foreach ( $this->formParts as &$parts )
		{
			foreach( $parts as &$p )
			{
				if ( !isset($p["sql_col"]) )
					continue;
					
				// we need to make sure the column also exists in the table, because if it doesn't sql query will fail
				$sql_col = $p["sql_col"];
				if ( !array_key_exists($sql_col, $arSqlCols) )
					continue;
					
				$cols.= $p["sql_col"].",";
			}
		}
		
		$cols = rtrim( $cols, "," );
		
		return $cols;
	}
	
	//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	//
	//	getRowsSql
	//
	//	$param
	//		where			: 
	//		col
	//		cols			: array of columns
	//		no_trashed
	//		setkey
	//		mapping
	//
	// development:
	// 	this is an example why i can't create sql querys.. because if I add a new value and if not all rows have set this value then query fails.
	// 	select * FROM (SELECT firstname, IFNULL(trashed,"false") as t FROM `ttfmembers`) as mytab where t!="true";	
	//
	//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	static public function getRowsSql( $param=array(
		"setkey" => "id",
		"no_trashed" => "true"
	))
	{
		$where = "";
		if ( isset($param["where"]) )
		{
			$where.= " WHERE ";
			foreach ( $param["where"] as $col => $value )
			{
				$where.= $col."='".$value."' AND ";
			}
			
			$where = rtrim( $where, " AND " );
		}
		
		if ( isset($param["where find_in_set"]) )
		{
			$sqlcolValue = $param["where find_in_set"]["value"];
			$sqlcolCompare = $param["where find_in_set"]["col"];
			$where = " WHERE FIND_IN_SET('".$sqlcolValue."',".$sqlcolCompare.")";
		}		

		$table = static::getTable();
		$query = "SELECT * FROM ".$table.$where;
		$arResults = R::getAll( $query );			// get all

		// special case if the uses the singular col then we return an associated array list with id and col
		if ( isset( $param["col"] ) )
		{
			$param["cols"] = array( "id", $param["col"] );
		}
		
		// filter trashed rows
		// note: I would like to move this to the actual query so we don't pull more than needed,
		// the problem is that some tables don't actually have the trashed column and the sql query will
		// always fail. I have tried to resolve this problem without any luck... my sql knowledge is too
		// limited right now to be able to solve this problem.
		if ( isset($param["no_trashed"]) && ($param["no_trashed"]=="true" || $param["no_trashed"]==true) )
		{
			$ar = array();
			
			foreach( $arResults as $a )
			{
				if ( isset($a["trashed"]) && $a["trashed"]=="true" )
					continue;
					
				$ar[] = $a;
			}

			$arResults = $ar;
		}
		
		// change key to have a specific column value
		if ( isset($param["setkey"]) )
		{
			$ar = array();
			foreach( $arResults as $k => $a )
			{
				// key defined by user
				$key = $a[$param["setkey"]];
				$ar[$key] = $a;
			}			
			$arResults = $ar;
		}
		
		// only return columns that are requested 
		if ( isset($param["cols"]) )
		{
			$cols = $param["cols"];
			
			if ( count( $cols ) )
			{
				$ar = array();
				
				foreach( $arResults as $k => $a )
				{
					$row = array();
					foreach ( $cols as $c )
					{
						$c = trim( $c );				
						$row[$c]  = $a[$c];
					}
					$ar[$k] = $row;
				}
				
				$arResults = $ar;
			}
		}	
		
		// handle mapping
		if ( isset($param["mapping"]) )
		{
			$mapping = $param["mapping"];
			foreach( $arResults as &$a )
			{
				foreach ( $mapping as $col => $newcol )
				{
					$a[$newcol] = $a[$col];
					unset($a[$col]);
				}
			}
		}	

		// special case: modify list so it's an associated id => col
		if ( isset( $param["col"] )	)	
		{
			$col = $param["col"];
			$list = array();
			foreach( $arResults as $a )
			{
				$list[$a["id"]] = $a[$col];
			}
			
			$arResults = $list;
		}
		
		return $arResults;
	}

	//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	//
	// getRows
	//
	//	maps a key to value where value is sql_col
	//
	//	$ar : 	array(
	//				"value" 	=> "id",
	//				"content" 	=> "name"
	//			)
	//
	//	if $ar is not an array then it assumes $ad is column id
	//
	//	Usage:
	//
	//	getRows();
	//
	//	getRows(array(
	//		"id" 		=> "id",
	//		"content" 	=> "name"
	//	));
	//
	//	getRows(
	//		array(
	//			"id" 		=> "id",
	//			"content" 	=> "name"
	//		),
	//		"colname",
	//		"colvalue"
	//	);
	//
	//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
	
	public function getRows( $ar=null, $sqlcolCompare=null, $sqlcolValue=null, $find_in_set=false )
	{
	/*
		echo "<textarea>";
		debug_print_backtrace();
		echo "</textarea><br/>";
	*/
	
		$where = "";
	
		if ( $sqlcolCompare!=null )
		{
			if ( $find_in_set )
				$where = " WHERE FIND_IN_SET('".$sqlcolValue."',".$sqlcolCompare.")";
			else
				$where = " WHERE ".$sqlcolCompare."='".$sqlcolValue."'";
		}

		// getSqlColumns insures that we only get the values that we actually use (there might be old columns in the table)
		$cols = $this->getSqlColumns();

		// sanity check
		if ( $cols=="" )
		{
			//echo "ERROR: spFormDatabase::getRows cols is empty<br/>";
			//spStackTrace();
			return array();
		}		
		
		$table = static::getTable();
		$arResults = R::getAll( "SELECT ".$cols." FROM ".$table.$where );
		
		// if $ar is null,.. then return all columns
		if ( $ar==null )
		{
			return $arResults;
		}
		
		$list = array();
		
		if ( is_array($ar) )
		{
			foreach ( $arResults as $a )
			{
				$data = array();
				
				foreach ( $ar as $k => $v )
				{ 
					if ( !isset($a[$v]) )
						$data[$k] = "WARNING: (".$v. ") not found";
					else
						$data[$k] = $a[$v];
				}
				
				$list[] = $data;
			}
		
			return $list;
		}
		
		// create simple association list id => column , where column is defined by $ar
		foreach ( $arResults as $a )
			$list[$a["id"]] = $a[$ar];
		

		return $list;
	}
	
	public function getRowsWhere( $arWhere )
	{
		$where = " ";
		if ( count($arWhere) )
		{
			$where = " WHERE";
		
			foreach( $arWhere as $k => $v )
			{
				$where.= " ".$k."='".$v."'";
			}
		}
	
		$table = static::getTable();
		$query = "SELECT * FROM ".$table.$where;

		$arResults = R::getAll( $query );
		return $arResults;
	}
	
	public function setValue( $sql_col, $value=null )
	{
		if ( is_array($sql_col) )
		{
			foreach( $sql_col as $k => $v )
			{
				$this->setValue( $k, $v );
			}
		}
	
		if ( !count($this->formParts) )
			return false;
	
		// located the sql_col
		foreach ( $this->formParts as &$parts )
		{
			foreach( $parts as &$p )
			{
				if ( $p["sql_col"]==$sql_col )
				{
					// special case if it's an element type
					if ( $p["type"]=="element" )
						$p["element"]->setElementDataString( $value );
				
				
					$p["value"] = $value;
					return;	// value found, no need to continue
				}
			}
		}
		
		return false;
	}
	
	// gets value from defintions
	public function getValue( $sql_col )
	{
		if ( !count($this->formParts) )
			return false;
	
		// located the sql_col
		foreach ( $this->formParts as &$parts )
		{
			foreach( $parts as &$p )
			{
				if ( isset($p["sql_col"]) && $p["sql_col"]==$sql_col )
				{
					if ( !isset($p["value"]) )
						$p["value"] = "";
				
					return $p["value"];
				}
			}
		}
		
		return false;
	}
	
	public function trash()
	{
		$this->setValue( "trashed", "true" );
		return $this->save();
	}
	
	public function delete()
	{
		R::trash( $this->bean );
		return true;
	}	
	
	public function wipe()
	{
		$table = static::getTable();
		R::wipe( $table );
		return true;
	}
	
	public function count()
	{
		if ( !class_exists( "R" ) )
			return false;
	
		$table = static::getTable();
		return R::count($table);
	}
	
	public function batchCreate( $batch )
	{
		$table = static::getTable();
		
		foreach ( $batch as $b )
		{
			$bean = R::dispense( $table );
			
			foreach ( $b as $k => $v )
			{
				$bean->$k = $v;
			}
			
			R::store( $bean );
		}
	}
	
	// use this as the way to get the list (after time remove the getGuiListBoxItems and getGuiListBoxItems2)
	const LISTTYPE_LISTBOX 	= 1;	
	const LISTTYPE_ASSOC 	= 2;	
	static public function getList( $listType=spFormDatabase::LISTTYPE_LISTBOX )
	{
		$ar = array();
		
		if ( $listType==spFormDatabase::LISTTYPE_LISTBOX )
		{
			$ar = static::getRowsSql(array(
				"cols" => array( 
					"id",
					static::getSqlIdentity()
				),
				"mapping" => array(
					"id" => "value",
					static::getSqlIdentity() => "content"
				),
				"no_trashed" => "true"
			));
		}

		if ( $listType==spFormDatabase::LISTTYPE_ASSOC )
		{
			$ar = static::getRowsSql(array(
				"cols" => array( 
					static::getSqlIdentity()
				),
				"setkey" => "id",
				"no_trashed" => "true"
			));
		}

		return $ar;
	}
	
	static public function getGuiListBoxItems()
	{
		$ar = static::getRowsSql(array(
			"cols" => array( 
				"id",
				static::getSqlIdentity()
			),
			"mapping" => array(
				"id" => "value",
				static::getSqlIdentity() => "content"
			),
			"no_trashed" => "true"
		));
	
		return $ar;
	}
	
	// this becomes very slow when loading many items because of the instancing
	static public function getGuiListBoxItems2( $bAddEmptyInfront=false )
	{
		// get all id's
		$ar = static::getRowsSql(array(
			"cols" => array( 
				"id"
			),
			"no_trashed" => "true"
		));

		//$class = get_class( $this );
		$class = get_called_class();
		$list = array();
		foreach ( $ar as $a )
		{
			$db = new $class($a);
			$list[] = array(
				"value" => $a["id"],
				"content" => $db->getIdentityTitle()
			);
		}
		
		if ( $bAddEmptyInfront )
		{
			array_unshift( $list, array(
				"value" 	=> "",
				"content" 	=> ""
			));	
		}
	
		return $list;
	}
	
	public function getAll( $no_trashed=true )
	{
		$dbClass = get_called_class();
		$ar = $dbClass::getRowsSql(array(
			"cols" => array("id"),
			"no_trashed" => $no_trashed
		));
		
		$list = array();
		foreach ( $ar as $a )
		{
			$list[] = new $dbClass( $a["id"] );
		}
		
		return $list;
	}
	
	static function getMatch( $param )
	{
		$table = static::getTable();
	
		$where = "";
		foreach ( $param as $k => $v )
		{
			$where.= $k."='".$v."' AND ";
		}
		$where = rtrim( $where, " AND " );
	
		$query = "
			SELECT
				*
			FROM
				{$table}
			WHERE
				{$where}
		";
		//echo $query."</br>";
		$ar = R::getAll( $query );
		return $ar;
	}
	
	static function hasParam( $param )
	{
		//echo $query."</br>";
		$ar = static::getMatch( $param );
		if ( count($ar) )
			return true;
			
		return false;
	}
	
}


?>